from fastapi import FastAPI, Query
from pydantic import BaseModel
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse, JSONResponse
from fastapi.middleware.cors import CORSMiddleware
from datetime import datetime
import uvicorn
import sqlite3
import os
import webbrowser
import sys
import pandas as pd
import socket
import logging

# ========== 日志配置 ==========
LOG_FILE = "log.log"
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    handlers=[
        logging.FileHandler(LOG_FILE, encoding="utf-8"),
        logging.StreamHandler(sys.stdout)  # 控制台也输出
    ]
)
logger = logging.getLogger(__name__)

app = FastAPI()

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# ========== 静态文件 ==========
try:
    current_dir = os.path.dirname(os.path.abspath(__file__))
    index_path = os.path.join(current_dir, "static", "index.html")
    static_dir = os.path.join(current_dir, "static")
    if not os.path.exists(static_dir):
        logger.warning(f"⚠️ 静态目录不存在: {static_dir}")
    app.mount("/static", StaticFiles(directory=static_dir), name="static")
except Exception as e:
    logger.exception("❌ 挂载静态文件失败")

DB_FILE = "scanDB.db"
EXPORT_DIR = "exports"

# ========== 数据库初始化 ==========
def init_db():
    try:
        conn = sqlite3.connect(DB_FILE)
        c = conn.cursor()
        c.execute("""
        CREATE TABLE IF NOT EXISTS scan_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            group_id INTEGER,
            package_code TEXT,
            supplier_code TEXT,
            result TEXT,
            timestamp TEXT
        )
        """)
        conn.commit()
        conn.close()
    except Exception as e:
        logger.exception("❌ 数据库初始化失败")

init_db()

def get_connection():
    try:
        return sqlite3.connect(DB_FILE)
    except Exception as e:
        logger.exception("❌ 打开数据库失败")
        raise

class ScanData(BaseModel):
    group_id: int
    package_code: str | None
    supplier_code: str | None
    result: str
    timestamp: str

# ========== API ==========
@app.post("/api/scan")
def add_scan(data: ScanData):
    try:
        conn = get_connection()
        c = conn.cursor()
        c.execute(
            "INSERT INTO scan_history (group_id,package_code, supplier_code, result, timestamp) VALUES (?, ?, ?, ?, ?)",
            (data.group_id, data.package_code, data.supplier_code, data.result, data.timestamp)
        )
        conn.commit()
        conn.close()
        return {"status": "ok", "message": "scan record saved"}
    except Exception as e:
        logger.exception("❌ 保存记录失败")
        return JSONResponse(content={"error": str(e)}, status_code=500)

@app.get("/api/query")
def query_scans(
    start: str = Query(None),
    end: str = Query(None),
):
    try:
        if not start or not end:
            today = datetime.now().date()
            start = f"{today} 00:00:00"
            end = f"{today} 23:59:59"

        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id, group_id, package_code, supplier_code, result, timestamp "
            "FROM scan_history WHERE timestamp BETWEEN ? AND ? ORDER BY timestamp",
            (start, end),
        )
        rows = cursor.fetchall()
        conn.close()
        return [
            {
                "id": r[0],
                "group_id": r[1],
                "package_code": r[2],
                "supplier_code": r[3],
                "result": r[4],
                "timestamp": r[5],
            }
            for r in rows
        ]
    except Exception as e:
        logger.exception("❌ 查询失败")
        return JSONResponse(content={"error": str(e)}, status_code=500)

@app.delete("/api/delete")
def delete_scans(
    start: str = Query(None),
    end: str = Query(None),
):
    try:
        if not start or not end:
            today = datetime.now().date()
            start = f"{today} 00:00:00"
            end = f"{today} 23:59:59"

        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(
            "DELETE FROM scan_history WHERE timestamp BETWEEN ? AND ?",
            (start, end),
        )
        count = cursor.rowcount
        conn.commit()
        conn.close()
        return {"deleted": count}
    except Exception as e:
        logger.exception("❌ 删除失败")
        return JSONResponse(content={"error": str(e)}, status_code=500)

@app.get("/api/export")
def export_scans(
    start: str = Query(None),
    end: str = Query(None),
):
    try:
        if not start or not end:
            today = datetime.now().date()
            start = f"{today} 00:00:00"
            end = f"{today} 23:59:59"

        conn = get_connection()
        df = pd.read_sql_query(
            "SELECT id, group_id, package_code, supplier_code, result, timestamp "
            "FROM scan_history WHERE timestamp BETWEEN ? AND ? ORDER BY timestamp",
            conn,
            params=(start, end),
        )
        conn.close()

        if df.empty:
            return JSONResponse(content={"message": "没有符合条件的数据"}, status_code=404)

        filename = f"export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        filepath = os.path.join(EXPORT_DIR, filename)

        # 仅在导出时创建目录
        os.makedirs(EXPORT_DIR, exist_ok=True)

        df.to_excel(filepath, index=False)

        return FileResponse(
            filepath,
            filename=filename,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )
    except Exception as e:
        logger.exception("❌ 导出失败")
        return JSONResponse(content={"error": str(e)}, status_code=500)


@app.get("/")
async def serve_frontend():
    if not os.path.exists(index_path):
        logger.error(f"❌ index.html 不存在: {index_path}")
        return JSONResponse(content={"error": "前端页面缺失"}, status_code=404)
    return FileResponse(index_path)

def start_browser():
    try:
        # url = "http://localhost:8000"
        # 获取本机计算机名和IP
        hostname = socket.gethostname()
        local_ip = socket.gethostbyname(hostname)
        url = f"http://{local_ip}:8000"
        webbrowser.open_new_tab(url)
    except Exception as e:
        logger.exception("⚠️ 打开浏览器失败")

if __name__ == "__main__":
    start_browser()
    try:
        uvicorn.run("main:app", host="0.0.0.0", port=8000, reload=False)
    except Exception as e:
        logger.exception("❌ FastAPI 服务启动失败")
